package com.lanju.dao;

import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.SQLQuery;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import com.lanju.entity.House;
import com.lanju.entity.HouseImg;
import com.lanju.entity.HousePrice;
import com.lanju.entity.HouseZuKeInfo;

@Repository
public class HouseDao extends BaseDao<House, String> {

	private static final Log log = LogFactory.getLog(HouseDao.class);
	@Autowired
	SessionFactory sessionFactory;

	@PersistenceContext(unitName = "financialmarketingUnit")
	private EntityManager entityManager;

	@SuppressWarnings("unchecked")
	public List<House> queryHouseList1(Map<String, Object> param) {
		try {
			int offset = 0;
			int limit = 0;
			if (!StringUtils.isEmpty(param.get("offset"))
					&& !StringUtils.isEmpty(param.get("limit"))) {
				offset = Integer.parseInt(param.get("offset").toString());
				limit = Integer.parseInt(param.get("limit").toString());
			}
			StringBuffer sql = new StringBuffer();
			sql.append("from House h where status in ('1','2')");
			System.out.println(param.get("search") != null);
			if (!StringUtils.isEmpty(param.get("search"))) {
				sql.append(" and h.houseName like '%" + param.get("search")
						+ "%' or h.address like '%" + param.get("search")
						+ "%' " + " or h.city like '%" + param.get("search")
						+ "%' or h.area like '%" + param.get("search")
						+ "%' or h.houseLayout like '%" + param.get("search")
						+ "%' or h.towards like '%" + param.get("search")
						+ "%'");
			}
			if (param.get("flagArr") != null && !param.get("flagArr").toString().equals("[]")) {
				String flagArrStr = param.get("flagArr").toString();
				String temp = flagArrStr.replace("\"", "'");
				String[] flagarr1 = flagArrStr.split(",");
				System.out.println(temp);
				System.out.println(temp.substring(1, temp.length()-1));
				sql.append("and  h.houseLayout in (" + temp.substring(1, temp.length()-1) +")");
				sql.append(" or h.towards in ("+ temp.substring(1, temp.length()-1) +") " );
				String[] tempArr = temp.substring(1, temp.length()-1).replace("'", "").split(",");
				System.out.println(tempArr);
				for(int i=0;i<tempArr.length;i++){
					sql.append(" or h.flag like '%"+ tempArr[i] +"%'" );
				}
//				sql.append(" )");
				System.out.println(sql);
			}
			if (!StringUtils.isEmpty(param.get("type"))) {
				sql.append(" and h.type = " + param.get("type") + "");
			}
			if (!StringUtils.isEmpty(param.get("orderBy"))) {
				sql.append(" order by " + param.get("orderBy") + "");
			} else {
				// sql.append(" order by h.createTime desc") ;
			}

			Query query = entityManager.createQuery(sql.toString());

			if (!StringUtils.isEmpty(param.get("offset"))
					&& !StringUtils.isEmpty(param.get("limit"))) {
				return query.setFirstResult(offset).setMaxResults(limit)
						.getResultList();
			} else {
				return query.getResultList();
			}

		} catch (Exception e) {
			log.error("queryHouseList fail", e);
		}
		return null;
	}

	@SuppressWarnings("unchecked")
	public long countHouseList1(Map<String, Object> param) {
		try {
			int offset = 0;
			int limit = 0;
			if (!StringUtils.isEmpty(param.get("offset"))
					&& !StringUtils.isEmpty(param.get("limit"))) {
				offset = Integer.parseInt(param.get("offset").toString());
				limit = Integer.parseInt(param.get("limit").toString());
			}

			StringBuffer sql = new StringBuffer();
			sql.append("select count(*) from House h where status in ('1','2') ");

			if (!StringUtils.isEmpty(param.get("search"))) {
				sql.append(" and h.houseName like '%" + param.get("search")
						+ "%' or h.address like '%" + param.get("search")
						+ "%' " + " or h.city like '%" + param.get("search")
						+ "%' or h.area like '%" + param.get("search")
						+ "%' or h.houseLayout like '%" + param.get("search")
						+ "%' or h.towards like '%" + param.get("search")
						+ "%'");
			}
			if (!StringUtils.isEmpty(param.get("flagStr"))) {
				sql.append("and  h.houseLayout in ("
						+ param.get("flagStr").toString()
						+ ") or h.towards in ("
						+ param.get("flagStr").toString() + ") ");
				sql.append(" or h.flag like '%"
						+ param.get("flagStr1")
								.toString()
								.substring(
										1,
										param.get("flagStr1").toString()
												.length() - 1) + "%' ");
			}
			if (!StringUtils.isEmpty(param.get("type"))) {
				sql.append(" and h.type = " + param.get("type") + "");
			}
			if (!StringUtils.isEmpty(param.get("orderBy"))) {
				sql.append(" order by " + param.get("orderBy") + "");
			} else {
				sql.append(" order by h.createTime desc");
			}

			Query query = entityManager.createQuery(sql.toString());
			return (long) query.getSingleResult();

		} catch (Exception e) {
			log.error("queryHouseList fail", e);
			return 0;
		}

	}

	public int countHouseList(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"select count(*) from ( SELECT	d.*,e.distance,e.flagName location,f.imgUrl,g.flagName layout,g2.flagName toward,g3.flagName subway,g4.flagName sq,g5.flagName busStop"
							+ " FROM lj_house d"
							+ "	LEFT JOIN (SELECT b.*,c.flagName FROM"
							+ "	( SELECT min( a.distance ) distance, a.flagId, a.houseId FROM lj_house_location a GROUP BY a.houseId ) b"
							+ "	LEFT JOIN lj_house_flag c ON b.flagId = c.flagId "
							+ "	) e ON d.houseId = e.houseId left join (SELECT b.imgId,b.houseId,b.imgUrl  FROM lj_house_img b where b.type = '1' group BY b.houseId)f "
							+ "	on d.houseId = f.houseId left join (select t3.houseId,t4.flagName from "
							+ "	(select t1.houseId,t2.flagId from lj_house t1 left join lj_house_flag_rel t2 on t1.houseId = t2.houseId)t3 left join lj_house_flag t4 on t3.flagId = t4.flagId where t4.type = '1'"
							+ "	 )g on d.houseId = g.houseId"
							+ " LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 2"
							+ "    ) g2 ON d.houseId = g2.houseId"
							+ " LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 7"
							+ "    ) g3 ON d.houseId = g3.houseId"
							+ "		LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 8"
							+ "    ) g4 ON d.houseId = g4.houseId"
							+ "			LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 10"
							+ "    ) g5 ON d.houseId = g5.houseId"
							+ ") h where 1=1");

			if (!StringUtils.isEmpty(param.get("search"))) {
				sql.append(" and h.houseName like '%" + param.get("search")
						+ "%' or h.address like '%" + param.get("search")
						+ "%' " + " or h.city like '%" + param.get("search")
						+ "%' or h.area like '%" + param.get("search")
						+ "%' or h.layout like '%" + param.get("search")
						+ "%' or h.toward like '%" + param.get("search")
						+ "%' or h.subway like '%" + param.get("search") + "%'"
						+ " or h.sq like '%" + param.get("search")
						+ "%' or h.busStop like '%" + param.get("search")
						+ "%'");
			}
			if (!StringUtils.isEmpty(param.get("flagStr"))) {
				sql.append(" and h.houseId in (select DISTINCT(t2.houseId) from lj_house_flag t1 LEFT join lj_house_flag_rel t2 on "
						+ "t1.flagId = t2.flagId where t1.flagId in ("
						+ param.get("flagStr")
						+ ")  and t2.houseId is not null)");
			}

			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());
			int count = Integer.valueOf(query.uniqueResult().toString());
			return count;
		} catch (Exception e) {
			log.error("countHouseList fail", e);
		}
		return 0;
	}

	public List<Map<String, Object>> queryImgByHouseId(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer("");

			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryImgByHouseId fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryLocationByHouseId(
			Map<String, Object> map) {
		try {

			StringBuffer sql = new StringBuffer("");

			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryImgByHouseId fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryAllFlag(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer("select * from lj_house_flag order by type");
			SQLQuery query = this.getSessionFactory().getCurrentSession().createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryAllFlag fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryHomeDetailByHouseId(
			Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"SELECT b.*,c.distance,d.flagName,e.phone,e.headImg,e.msg,e.nickName,g1.flagName toward,"
							+ " g2.flagName layout,g3.flagName RentTime,g3.flagType rentTimeType,g4.flagName yj,g5.imgUrl communityUrl"
							+ " FROM lj_agreement a left join lj_house b on a.houseId = b.houseId "
							+ " left join"
							+ " (SELECT min( a.distance ) distance, a.flagId, a.houseId FROM lj_house_location a GROUP BY a.houseId)c on b.houseId = c.houseId"
							+ " left join lj_house_flag d on c.flagId = d.flagId "
							+ " left join lj_user e on a.chargeUserId = e.userId"
							+ " LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 2"
							+ "    ) g1 ON b.houseId = g1.houseId"
							+ "	LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 1"
							+ "    ) g2 ON b.houseId = g2.houseId"
							+ "		LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName,t4.type flagType"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type in ('4','5') "
							+ "    ) g3 ON b.houseId = g3.houseId"
							+ "		LEFT JOIN ("
							+ "    SELECT t3.houseId"
							+ "        ,t4.flagName"
							+ "    FROM ("
							+ "        SELECT t1.houseId"
							+ "            ,t2.flagId"
							+ "        FROM lj_house t1"
							+ "        LEFT JOIN lj_house_flag_rel t2 ON t1.houseId = t2.houseId"
							+ "        ) t3"
							+ "			JOIN lj_house_flag t4 ON t3.flagId = t4.flagId AND t4.type = 6"
							+ "    ) g4 ON b.houseId = g4.houseId"
							+ "		left join (select houseId,imgUrl from lj_house_img   where type = '3')g5 on b.houseId = g5.houseId"
							+ " where b.houseId = '"
							+ param.get("houseId")
							+ "' ");
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryHomeDetailByHouseId fail", e);
		}
		return null;
	}

	public House queryHomeDetailByHouseId1(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer("from House where houseId='"
					+ param.get("houseId").toString() + "'");
			Query query = entityManager.createQuery(sql.toString());
			return (House) query.getSingleResult();
		} catch (Exception e) {
			log.error("queryHomeDetailByHouseId fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryHomeLocationByHouseId(
			Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"select c.*,d.flagName,d.type from "
							+ " (select * from lj_house_location a where a.houseId = '"
							+ param.get("houseId")
							+ "')c left join (select * from lj_house_flag b where b.type in ('7','8','9','10'))d on c.flagId = d.flagId");
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryAllFlag fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryHomeImgByHouseId(
			Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"select * from lj_house_img a where a.type = '2' and  a.houseId = '"
							+ param.get("houseId") + "' ");
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryAllFlag fail", e);
		}
		return null;
	}

	public List<Map<String, Object>> queryHomeFlagByHouseId(
			Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"select * from lj_house_flag b where b.type = '3' and b.flagId in (select DISTINCT(a.flagId) from lj_house_flag_rel a where a.houseId = '"
							+ param.get("houseId") + "' )");
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());

			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryAllFlag fail", e);
		}
		return null;
	}

	public List<HouseImg> getHouseIMgList(String houseId, String type) {
		try {
			StringBuffer sql = new StringBuffer();
			sql.append("from HouseImg where houseId='" + houseId + "'");
			if (type != "") {
				sql.append(" and type ='" + type + "'");
			}
			Query query = entityManager.createQuery(sql.toString());
			return query.getResultList();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Map> queryAPList(Map<String, Object> param) {
		try {
			StringBuffer sql = new StringBuffer();
			sql.append(" select * from " + param.get("table")
					+ " where userId = '" + param.get("userId") + "' order by "
					+ param.get("orderBy") + " desc ");
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());
			return query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
					.list();
		} catch (Exception e) {
			log.error("queryServiceList fail", e);
		}
		return null;
	}

	public List<House> queryMyHouseList(Map<String, Object> param) {
		try {
			int offset = 0;
			int limit = 0;
			if (!StringUtils.isEmpty(param.get("offset"))
					&& !StringUtils.isEmpty(param.get("limit"))) {
				offset = Integer.parseInt(param.get("offset").toString());
				limit = Integer.parseInt(param.get("limit").toString());
			}
			StringBuffer sql = new StringBuffer();
			sql.append("from House h where status=1");
			sql.append("and  h.houseId in (" + param.get("apStr").toString()
					+ ") ");

			Query query = entityManager.createQuery(sql.toString());

			if (!StringUtils.isEmpty(param.get("offset"))
					&& !StringUtils.isEmpty(param.get("limit"))) {
				return query.setFirstResult(offset).setMaxResults(limit)
						.getResultList();
			} else {
				return query.getResultList();
			}

		} catch (Exception e) {
			log.error("queryMyHouseList fail", e);
		}
		return null;
	}

	public long countMyHouseList(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer();
			sql.append("select count(*) from House h where status=1 ");

			sql.append(" and  h.houseId in (" + param.get("apStr").toString()+ ") ");

			Query query = entityManager.createQuery(sql.toString());
			return (long) query.getSingleResult();

		} catch (Exception e) {
			log.error("countMyHouseList fail", e);
			return 0;
		}
	}

	public void addHousePrice(HousePrice hp) {
		try {
			entityManager.persist(hp);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
    public HouseZuKeInfo getHouseZuKeInfo(Map<String,Object> map){
    	try {
   		 Query query =entityManager.createQuery("from HouseZuKeInfo where status=1 and userId='"+map.get("userId")+"' and houseId='"+map.get("houseId").toString()+"'");
   		return (HouseZuKeInfo)query.getResultList().get(0);
		} catch (Exception e) {
			e.printStackTrace();
		 return null;
		}
    }
	
    
    public House getHouseByAddr(Map<String,Object> map){
    	try {
   		 Query query =entityManager.createQuery("from House where  houseName='"+map.get("houseName").toString());
   		return (House) query.getSingleResult();
		} catch (Exception e) {
			e.printStackTrace();
		 return null;
		}
    }

	public boolean queryCollectByHouseId(Map<String, Object> param) {
		try {

			StringBuffer sql = new StringBuffer(
					"select * from  lj_collect where 1=1");
			if (!StringUtils.isEmpty(param.get("userId"))) {
				sql.append(" and userId = '" + param.get("userId") + "'");
			}
			if (!StringUtils.isEmpty(param.get("houseId"))) {
				sql.append(" and houseId = '" + param.get("houseId") + "'");
			}
			SQLQuery query = this.getSessionFactory().getCurrentSession()
					.createSQLQuery(sql.toString());
			List list = query.setResultTransformer(
					Transformers.ALIAS_TO_ENTITY_MAP).list();
			if (list.size() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			log.error("queryCollectByHouseId fail", e);
			return false;
		}
	}

}
